﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using QLQTPM_ThuDuc_Group7.DTO;
using System.Windows.Forms;
using DesignLayOut_QLQTPM_Team7.DAO;

namespace QLQTPM_ThuDuc_Group7.DAO
{
    class DiaDiemDAO
    {
        public static DataTable LoadDuLieu()
        {
            string sql = "select dl.MaDuLieu, dl.MaDichVu, dv.TenDichVu, dl.MaTenDiaDiem, tdd.TenDiaDiem, dl.SoNha, dl.MaDuong, d.TenDuong, dl.MaPhuong, p.TenPhuong, dl.MaQuanHuyen, qh.TenQuanHuyen, dl.MaTinhThanh, tt.TenTinhThanh, dl.KinhDo, dl.ViDo, dl.ChuThich from DULIEU dl, TENDIADIEM tdd, PHUONG p, DUONG d, QUANHUYEN qh, TINHTHANH tt, DICHVU dv where dl.MaTenDiaDiem=tdd.MaTenDiaDiem and dl.MaDichVu=dv.MaDichVu and dl.MaDuong=d.MaDuong and dl.MaPhuong=p.MaPhuong and dl.MaQuanHuyen=qh.MaQuanHuyen and dl.MaTinhThanh=tt.MaTinhThanh";
            DataProvider dtb = new DataProvider();
            return dtb.executeQuery(sql);
        }
        //----Delete
        //public static DataTable LayMaTenDiaDiem(string tendiadiem)
        //{
        //    DataProvider dtb = new DataProvider();
        //    dtb.connect();
        //    string sql = "select * from TenDiaDiem where TenDiaDiem=" + tendiadiem;
        //    return dtb.executeQuery(sql);
        //}
        public static bool XoaDiaDiem(int matendiadiem)
        {
            bool kq = true;
            DataProvider dp = new DataProvider();
            dp.connect();
            string sql = "delete from DuLieu where MaTenDiaDiem= " + matendiadiem;
            kq = dp.executeNonQuery(sql);
            if (kq == false )
                return kq;
            return kq;
        }
        //Ham cho Search
        public List<DiaDiemDTO> LayDSDiaDiemByTuKhoaDD_DichVu(int maDichVu, string tukhoa)
        {
            List<DiaDiemDTO> lstDiaDiem = new List<DiaDiemDTO>();
            DataProvider dp = new DataProvider();
            string sql = "SELECT DISTINCT DICHVU.TenDichVu, DULIEU.MaDuLieu, DULIEU.MaDichVu, DULIEU.MaTenDiaDiem, DULIEU.SoNha, DULIEU.MaDuong, DULIEU.MaPhuong, DULIEU.MaQuanHuyen, DULIEU.MaTinhThanh, DULIEU.KinhDo, DULIEU.ViDo, DULIEU.ChuThich, DUONG.TenDuong, PHUONG.TenPhuong, QUANHUYEN.TenQuanHuyen, TENDIADIEM.TenDiaDiem, TINHTHANH.TenTinhThanh "
            + "FROM (TENDIADIEM INNER JOIN (TINHTHANH INNER JOIN (QUANHUYEN INNER JOIN (PHUONG INNER JOIN (DUONG INNER JOIN (DICHVU INNER JOIN DULIEU ON DICHVU.[MaDichVu] = DULIEU.[MaDichVu]) ON DUONG.[MaDuong] = DULIEU.[MaDuong]) ON PHUONG.[MaPhuong] = DULIEU.[MaPhuong]) ON QUANHUYEN.[MaQuanHuyen] = DULIEU.[MaQuanHuyen]) ON TINHTHANH.[MaTinhThanh] = DULIEU.[MaTinhThanh]) ON TENDIADIEM.[MaTenDiaDiem] = DULIEU.[MaTenDiaDiem]) INNER JOIN TUKHOADIADIEM ON TENDIADIEM.[MaTenDiaDiem] = TUKHOADIADIEM.[MaTenDiaDiem] "
            + "WHERE DULIEU.[MADICHVU] = " + maDichVu + " AND TUKHOADIADIEM.[TUKHOATENDIADIEM] like '%" + tukhoa + "%'";

            DataTable dt = dp.executeQuery(sql);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DiaDiemDTO diadiem = new DiaDiemDTO();
                try
                {
                    diadiem.MaDuLieu = Convert.ToInt32(dt.Rows[i]["madulieu"]);
                    diadiem.MaDichVu = Convert.ToInt32(dt.Rows[i]["madichvu"]);
                    diadiem.MaTenDiaDiem = Convert.ToInt32(dt.Rows[i]["matendiadiem"]);
                    diadiem.TenDiaDiem = dt.Rows[i]["tendiadiem"].ToString();
                    diadiem.MaDuong = Convert.ToInt32(dt.Rows[i]["MaDuong"]);
                    diadiem.TenDuong = dt.Rows[i]["tenduong"].ToString();
                    diadiem.MaQuanHuyen = Convert.ToInt32(dt.Rows[i]["MaQuanHuyen"]);
                    diadiem.TenQuanHuyen = dt.Rows[i]["tenquanhuyen"].ToString();
                    diadiem.MaPhuong = Convert.ToInt32(dt.Rows[i]["MaPhuong"]);
                    diadiem.TenPhuong = dt.Rows[i]["tenphuong"].ToString();
                    diadiem.MaTinhThanh = Convert.ToInt32(dt.Rows[i]["MaTinhThanh"]);
                    diadiem.TenTinhThanh = dt.Rows[i]["tentinhthanh"].ToString();
                    diadiem.KinhDo = Convert.ToDouble(dt.Rows[i]["KinhDo"]);
                    diadiem.ViDo = Convert.ToDouble(dt.Rows[i]["ViDo"]);
                    diadiem.ChuThich = dt.Rows[i]["chuthich"].ToString();
                    diadiem.SoNha = dt.Rows[i]["sonha"].ToString();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    continue;
                }

                lstDiaDiem.Add(diadiem);
            }

            return lstDiaDiem;
        }
        //Ham cho Search
        public int ThemDanhSachDuLieu(List<DiaDiemDTO> lstDuLieu)
        {
            int dem = 0;//Đếm số lượng insert thành công

            DataProvider dp = new DataProvider();
            dp.connect();
            for (int i = 0; i < lstDuLieu.Count; i++)
            {
                DiaDiemDTO duLieu = lstDuLieu.ElementAt(i);

                string sql = "Insert into dulieu(madichvu, matendiadiem, sonha, maphuong, maQuanHuyen, matinhthanh, kinhdo, vido, chuthich)"
                    + "values (" + duLieu.MaDichVu + ", " + duLieu.MaTenDiaDiem + ", '" + duLieu.SoNha + "', " + duLieu.MaPhuong + ", " + duLieu.MaQuanHuyen + ", "
                    + "" + duLieu.MaTinhThanh + ", " + duLieu.KinhDo + ", " + duLieu.ViDo + ", '" + duLieu.ChuThich + "')";
                
                bool kq = false;
                try
                {
                    kq = dp.executeNonQuery(sql);
                }
                catch (Exception oleEX)
                {
                    //Log4Net
                    //Console.WriteLine("Dong thu " + temp + " bi loi:" + oleEX.Message);
                    Console.WriteLine(oleEX.Message);
                }
                if (kq)
                    dem++;
            }
            dp.disconnect();

            return dem;
        }
        //-----Bat dau insert----------
        DataProvider dataProvider = new DataProvider();

        //Hàm kiểm tra địa điểm đó đã có dưới CSDL chưa
        public bool KiemTraTenDiaDiemDaTonTai(string tenDiaDiem)
        {
            bool flag = false;
            OleDbConnection cn;
            try
            {
                cn = DataProvider.ConnectionData();
                string strSQL1 = "select * from tendiadiem where TenDiaDiem = '" + tenDiaDiem + "'";
                object kt1 = dataProvider.executeScalar(strSQL1);

                string strSQL2 = "select * from tukhoadiadiem where TuKhoaTenDiaDiem = '" + tenDiaDiem + "'";
                object kt2 = dataProvider.executeScalar(strSQL2);
                if (kt1 != null || kt2 != null)
                {
                    flag = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Tên địa điểm này đã có " + ex.Message);
            }
            return flag;
        }

        //Hàm kiểm tra địa chỉ insert có kinh doanh loại dịch vụ đang insert hay không
        public bool KiemTraDiaChiKinhDoanhCoTonTai(string soNha)
        {
            bool flag = false;
            OleDbConnection cn;
            try
            {
                cn = DataProvider.ConnectionData();
                string strSQL1 = "select * from dulieu where SoNha = '" + soNha + "'";
                object kt1 = dataProvider.executeScalar(strSQL1);

                if (kt1 != null)
                {
                    flag = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Địa chỉ này đã có " + ex.Message);
            }
            return flag;
        }

        //Hàm kiểm tra đia điểm đã tồn tại chưa
        public bool KiemTraTenDiaDiemVaDiaChiKinhDoanhCoTonTai(DiaDiemDTO diaDiemDTO)
        {
            bool flag = false;
            OleDbConnection cn;
            try
            {
                cn = DataProvider.ConnectionData();
                int maDichVu = diaDiemDTO.MaDichVu;
                int maTenDiaDiem = MaTenDiaDiem(diaDiemDTO.TenDiaDiem);
                string soNha = diaDiemDTO.SoNha;
                int maDuong = LayMaDuong(diaDiemDTO.TenDuong);
                int maPhuong = LayMaPhuong(diaDiemDTO.TenPhuong);
                int maQuanHuyen = LayMaQuanHuyen(diaDiemDTO.TenQuanHuyen);
                string strSQL1 = "select * from dulieu where MaDichVu = " + maDichVu + " and MaTenDiaDiem = " + maTenDiaDiem + " and SoNha = '" + soNha + "' and MaDuong = " + maDuong + " and MaPhuong = " + maPhuong + " and MaQuanHuyen = " + maQuanHuyen;
                object kt1 = dataProvider.executeScalar(strSQL1);

                if (kt1 != null)
                {
                    flag = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Địa chỉ này đã có " + ex.Message);
            }
            return flag;
        }


        //Hàm thêm địa điểm
        public bool ThemDiaDiem(DiaDiemDTO diaDiemDTO)
        {
            bool flag = false;
            string err = "";
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            dataProvider.connect();
            try
            {
                //bool flag1 = KiemTraTenDiaDiemDaTonTai(diaDiemDTO.TenDiaDiem);
                //bool flag2 = KiemTraDiaChiKinhDoanhCoTonTai(diaDiemDTO.SoNha);
                bool flag1 = KiemTraTenDiaDiemVaDiaChiKinhDoanhCoTonTai(diaDiemDTO);
                try
                {
                    //if (flag1 == true && flag2 == true)
                    if (flag1 == true)
                    {
                        err += "Tên địa điểm và địa chỉ này đã có trong cơ sở dữ liệu";
                        MessageBox.Show(err, "Lỗi !!!");
                    }
                    else
                    {
                        // B3: Tao chuoi strSQL thao tac CSDL
                        int maTenDiaDiem = MaTenDiaDiem(diaDiemDTO.TenDiaDiem);
                        int maDuong = MaDuong(diaDiemDTO.TenDuong);
                        int maPhuong = MaPhuong(diaDiemDTO.TenPhuong);
                        int maQuanHuyen = MaQuanHuyen(diaDiemDTO.TenQuanHuyen);
                        int maTinhThanh = 16;
                        string strSQL = "insert into dulieu (MaDichVu,MaTenDiaDiem,SoNha,MaDuong,MaPhuong,MaQuanHuyen,MaTinhThanh,KinhDo,ViDo,ChuThich) values (" + diaDiemDTO.MaDichVu + "," + maTenDiaDiem + "," + diaDiemDTO.SoNha + "," + maDuong + "," + maPhuong + "," + maQuanHuyen + "," + maTinhThanh + "," + diaDiemDTO.KinhDo + "," + diaDiemDTO.ViDo + ",'" + diaDiemDTO.ChuThich + "')";
                        bool co = dataProvider.executeNonQuery(strSQL);
                        if (co == true)
                        {
                            flag = true;
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi insert" + ex.Message);
            }
            return flag;
        }

        //Lấy mã đường ứng với tên đường nhâp ở textbox
        public int MaDuong(string tenDuong)
        {
            OleDbConnection cn;
            int ma = -1;
            try
            {
                cn = DataProvider.ConnectionData();
                // B3: Tao chuoi strSQL thao tac CSDL
                string strSQL = "select * from duong where TenDuong = '" + tenDuong + "'";
                object kt = dataProvider.executeScalar(strSQL);
                if (kt != null)
                {
                    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                    ma = (int)cmd.ExecuteScalar();
                }
                else
                {
                    strSQL = "insert into duong(TenDuong) values ('" + tenDuong + "')";
                    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                    dataProvider.executeNonQuery(strSQL);

                    strSQL = "select * from duong where TenDuong = '" + tenDuong + "'";
                    cmd = new OleDbCommand(strSQL, cn);
                    ma = (int)cmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi" + ex.Message);
            }
            return ma;
        }

        public int LayMaDuong(string tenDuong)
        {
            OleDbConnection cn;
            int ma = -1;
            try
            {
                cn = DataProvider.ConnectionData();
                // B3: Tao chuoi strSQL thao tac CSDL
                string strSQL = "select * from duong where TenDuong = '" + tenDuong + "'";
                object kt = dataProvider.executeScalar(strSQL);
                if (kt != null)
                {
                    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                    ma = (int)cmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi" + ex.Message);
            }
            return ma;
        }
        //Lấy mã phường ứng với tên phường nhâp ở textbox
        public int MaPhuong(string tenPhuong)
        {
            OleDbConnection cn;
            int ma = -1;
            try
            {
                cn = DataProvider.ConnectionData();
                // B3: Tao chuoi strSQL thao tac CSDL
                string strSQL = "select * from phuong where TenPhuong = '" + tenPhuong + "'";
                object kt = dataProvider.executeScalar(strSQL);
                if (kt != null)
                {
                    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                    ma = (int)cmd.ExecuteScalar();
                }
                else
                {
                    strSQL = "insert into phuong(TenPhuong) values ('" + tenPhuong + "')";
                    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                    dataProvider.executeNonQuery(strSQL);

                    strSQL = "select * from phuong where TenPhuong = '" + tenPhuong + "'";
                    cmd = new OleDbCommand(strSQL, cn);
                    ma = (int)cmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi" + ex.Message);
            }
            return ma;
        }

        public int LayMaPhuong(string tenPhuong)
        {
            OleDbConnection cn;
            int ma = -1;
            try
            {
                cn = DataProvider.ConnectionData();
                // B3: Tao chuoi strSQL thao tac CSDL
                string strSQL = "select * from phuong where TenPhuong = '" + tenPhuong + "'";
                object kt = dataProvider.executeScalar(strSQL);
                if (kt != null)
                {
                    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                    ma = (int)cmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi" + ex.Message);
            }
            return ma;
        }
        //Lấy mã quận huyện ứng với tên quận huyện nhâp ở textbox
        public int MaQuanHuyen(string tenQuanHuyen)
        {
            OleDbConnection cn;
            int ma = -1;
            try
            {
                cn = DataProvider.ConnectionData();
                // B3: Tao chuoi strSQL thao tac CSDL
                string strSQL = "select * from quanhuyen where TenQuanHuyen = '" + tenQuanHuyen + "'";
                object kt = dataProvider.executeScalar(strSQL);
                if (kt != null)
                {
                    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                    ma = (int)cmd.ExecuteScalar();
                }
                else
                {
                    strSQL = "insert into quanhuyen(TenQuanHuyen) values ('" + tenQuanHuyen + "')";
                    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                    dataProvider.executeNonQuery(strSQL);

                    strSQL = "select * from quanhuyen where TenPhuong = '" + tenQuanHuyen + "'";
                    cmd = new OleDbCommand(strSQL, cn);
                    ma = (int)cmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi" + ex.Message);
            }
            return ma;
        }

        public int LayMaQuanHuyen(string tenQuanHuyen)
        {
            OleDbConnection cn;
            int ma = -1;
            try
            {
                cn = DataProvider.ConnectionData();
                // B3: Tao chuoi strSQL thao tac CSDL
                string strSQL = "select * from quanhuyen where TenQuanHuyen = '" + tenQuanHuyen + "'";
                object kt = dataProvider.executeScalar(strSQL);
                if (kt != null)
                {
                    OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                    ma = (int)cmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi" + ex.Message);
            }
            return ma;
        }
        //Lấy mã tên địa điểm ứng với địa điểm nhâp ở textbox
        public int MaTenDiaDiem(string tenDiaDiem)
        {
            OleDbConnection cn;
            int ma = -1;
            try
            {
                cn = DataProvider.ConnectionData();
                // B3: Tao chuoi strSQL thao tac CSDL
                string strSQL = "insert into tendiadiem(TenDiaDiem) values ('" + tenDiaDiem + "')";
                OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                dataProvider.executeNonQuery(strSQL);

                strSQL = "select * from tendiadiem where TenDiaDiem = '" + tenDiaDiem + "'";
                cmd = new OleDbCommand(strSQL, cn);
                ma = (int)cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Lỗi" + ex.Message);
            }
            return ma;
        }

        public bool isNumberic(string str)
        {
            try
            {
                str = str.Trim();
                double foo = double.Parse(str);
                return (true);
            }
            catch (FormatException)
            {
                return (false);
            }
        }

        //Lấy Dữ liệu Địa Điểm theo mã tên địa điểm --Delete---
        public static DataTable LayDuLieuDDTheoMaTenDiaDiem(int matendiadiem)
        {
            DataTable tb = null;
            DataProvider dp = new DataProvider();
            dp.connect();
            string sql = "SELECT TENDIADIEM.MaTenDiaDiem, TENDIADIEM.TenDiaDiem, DULIEU.SoNha, DUONG.TenDuong, PHUONG.TenPhuong, QUANHUYEN.TenQuanHuyen, TINHTHANH.TenTinhThanh" +
                "FROM TINHTHANH INNER JOIN (TENDIADIEM INNER JOIN (QUANHUYEN INNER JOIN (PHUONG INNER JOIN (DUONG INNER JOIN DULIEU ON DUONG.MaDuong = DULIEU.MaDuong) ON PHUONG.MaPhuong = DULIEU.MaPhuong) ON QUANHUYEN.MaQuanHuyen = DULIEU.MaQuanHuyen) ON TENDIADIEM.MaTenDiaDiem = DULIEU.MaTenDiaDiem) ON TINHTHANH.MaTinhThanh = DULIEU.MaTinhThanh" +
                "WHERE (((TENDIADIEM.MaTenDiaDiem)=" + matendiadiem + "))";
            tb = dp.executeQuery(sql);
            return tb;
        }
        //---Ket thuc insert-----

        //-----Bat dau Update------
        public static DiaDiemDTO LoadThongTinDiaDiem(int ma)
        {
            string sql = "select * from DULIEU where MATENDIADIEM=" + ma;
            DataProvider dtb = new DataProvider();
            DataTable tb = dtb.executeQuery(sql);

            string sonha = tb.Rows[0]["SONHA"].ToString();
            int maduong = Convert.ToInt32(tb.Rows[0]["MADUONG"].ToString());
            int maphuong = Convert.ToInt32(tb.Rows[0]["MAPHUONG"].ToString());
            int maquanhuyen = Convert.ToInt32(tb.Rows[0]["MAQUANHUYEN"].ToString());
            int matinhthanh = Convert.ToInt32(tb.Rows[0]["MATINHTHANH"].ToString());
            int madichvu = Convert.ToInt32(tb.Rows[0]["MADICHVU"].ToString());
            double kinhdo = double.Parse(tb.Rows[0]["KINHDO"].ToString());
            double vido = double.Parse(tb.Rows[0]["VIDO"].ToString());
            string chuthich = tb.Rows[0]["CHUTHICH"].ToString();
            DiaDiemDTO dd = new DiaDiemDTO(ma, madichvu, sonha, maduong, maphuong, maquanhuyen, matinhthanh, kinhdo, vido, chuthich);
            return dd;
        }
        public static DataTable LoadTenDiaDiem()
        {
            string sql = "select * from TENDIADIEM";
            DataProvider dtb = new DataProvider();
            return dtb.executeQuery(sql);
        }

        public static bool SuaDiaDiem(DiaDiemDTO dd)
        {
            DataProvider dtb = new DataProvider();
            dtb.connect();
            string sql = "";
            sql = "update DULIEU set ";
            sql += "SoNha ='" + dd.SoNha + "',";
            sql += "MaDuong =" + dd.MaDuong + ",";
            sql += "MaPhuong =" + dd.MaPhuong + ",";
            sql += "MaQuanHuyen =" + dd.MaQuanHuyen + ",";
            sql += "MaTinhThanh =" + dd.MaTinhThanh + ",";
            sql += "MaDichVu =" + dd.MaDichVu + ",";
            sql += "KinhDo =" + dd.KinhDo + ",";
            sql += "ViDo =" + dd.ViDo + ",";
            sql += "ChuThich ='" + dd.ChuThich + "'";
            sql += " where MaTenDiaDiem =" + dd.MaTenDiaDiem;

            return dtb.executeNonQuery(sql);
        }
        //-----Ket thuc Updata------
    }
}
